package com.miao.util.poi;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;

import java.awt.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * EXCEL坐标转换
 */
public class AxisFormulaTrans {

    /**
     * 转换类似"B3"为 Point对象
     *
     * @param cellPos
     * @return Point 对象
     */
    @SuppressWarnings("unused")
    public static Point getCellPoint(String cellPos) {
        if (StringUtils.isBlank(cellPos))
            return null;
        CellReference cr = new CellReference(cellPos);
        if (cr == null)
            return null;
        else
            return new Point(cr.getRow(), cr.getCol());
    }

    /**
     * 转换Point对象为"B3"
     *
     * @return 类似"B3"这样的格式,参数超阈值,返回null
     */
    public static String getCellPos(Point point) {
        return getCellPos((int) point.getX(), (int) point.getY());
    }

    /**
     * 转换类似"2,1"为"B3"
     *
     * @return 类似"B3"这样的格式,参数超阈值,返回null
     */
    public static String getCellPos(int x, int y) {
        if ((x >= 0) && (y >= 0)) {
            CellReference cr = new CellReference(x, y);
            String formatStr = cr.formatAsString();
            return dropDollar(formatStr);

        } else
            return null;

    }

    /**
     * 工具小方法:去掉字串中的$
     *
     * @param str
     * @return 如果参数字串含有$, 就去掉它, 否则原则原样返回
     */
    public static String dropDollar(String str) {
        if ((!StringUtils.isBlank(str)) && (StringUtils.contains(str, '$'))) {
            String[] sbs = StringUtils.split(str, '$');
            return sbs[0] + sbs[1];
        } else
            return str;
    }

    /**
     * 返回一个CELL的 对应坐标(Point对象)
     *
     * @param cell
     * @return 返回Point对象
     */
    public static Point getCellPoint(Cell cell) {
        if (cell != null)
            return new Point(cell.getRowIndex(), cell.getColumnIndex());
        else
            return null;
    }

    /**
     * 返回一个CELL的 对应format(如"B3")
     *
     * @param cell
     * @return 返回String(如"B3")
     */
    public static String getCellFormatStr(Cell cell) {
        if (cell != null) {
            return getCellPos(cell.getRowIndex(), cell.getColumnIndex());
        } else
            return null;
    }

    /**
     * 转换类似0-25数字到字母
     *
     * @return 字母字符串, 参数超阈值, 返回null
     */
    public static String getValue(int key) {
        if ((key < 0) || (key > 25))
            return null;
        else {
            HashMap<String, String> maps = new HashMap<String, String>();
            {
                maps.put("0", "A");
                maps.put("1", "B");
                maps.put("2", "C");
                maps.put("3", "D");
                maps.put("4", "E");
                maps.put("5", "F");
                maps.put("6", "G");
                maps.put("7", "H");
                maps.put("8", "I");
                maps.put("9", "J");
                maps.put("10", "K");
                maps.put("11", "L");
                maps.put("12", "M");
                maps.put("13", "N");
                maps.put("14", "O");
                maps.put("15", "P");
                maps.put("16", "Q");
                maps.put("17", "R");
                maps.put("18", "S");
                maps.put("19", "T");
                maps.put("20", "U");
                maps.put("21", "V");
                maps.put("22", "W");
                maps.put("23", "X");
                maps.put("24", "Y");
                maps.put("25", "Z");
                return maps.get(Integer.toString(key));
            }
        }

    }

    /**
     * 获得类似"B3:F3"所代表的B3,C3,D3,E3,F3的Point列表,用于EXCEL表达式解析
     *
     * @param firstX
     * @param firstY
     * @param lastX
     * @param lastY
     * @return
     */
    public static List<Point> getAxisValue(int firstX, int firstY, int lastX, int lastY) {
        List<Point> pList = null;
        if (firstX == lastX) {
            pList = new ArrayList<Point>();
            for (int i = firstY; i <= lastY; i++) {
                pList.add(new Point(firstX, i));
            }
        }
        if (firstY == lastY) {
            pList = new ArrayList<Point>();
            for (int i = firstX; i <= lastX; i++) {
                pList.add(new Point(i, firstY));
            }
        }

        return pList;
    }

    public static void main(String[] args) {
        int x = 2;
        int y = 1;
        System.out.println(getCellPos(x, y));
        System.out.println(getCellPoint(getCellPos(x, y)));

        System.out.println(getValue(24));
    }

}
